<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of rank_model
 *
 * @author Marko
 */
class Rank_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }
    
    /**
     * Fetches data to display in table for admin rank's page. Table gives overview
     * of inserted rank data.
     * @param String $gender rank table gender
     * @return result array
     */
    public function fetch_rank_admin_table($gender) {
        $this->db->select('distance, stroke, time_ms, type')->from('eul_ranks')->where('gender', $gender);
        return $this->db->get()->result();
    }

    /**
     * Fetches data to display in table for clients.
     * @param type $gender rank table gender
     * @param type $stroke rank table stroke
     * @return result array
     */
    public function fetch_rank_tabel($gender, $stroke) {
        
        $query = $this->db->query("SELECT sub_a.id, min(r.result_ms) as ms, r.distance, 
            r.stroke, sub_a.last_name, sub_a.first_name, sub_a.age,
        COALESCE (
        (SELECT `type_code` FROM `eul_ranks` WHERE UPPER(`gender`) = UPPER('$gender') AND `distance` = r.distance 
        AND `stroke` = '$stroke' AND time_ms >= min(r.result_ms) order by time_ms asc LIMIT 1)
        , (SELECT max(type_code) FROM `eul_ranks`)) as rank
        FROM results as r,
        (SELECT athletes.id, athletes.last_name, athletes.first_name, athletes.gender, 
        YEAR(CURDATE()) - YEAR(athletes.dob) - (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(athletes.dob, '%m%d')) as age 
        FROM athletes, teams 
        WHERE teams.full_name LIKE 'Tartu Ujumisklubi' AND athletes.team_id = teams.id ) sub_a
        WHERE distance>=50 AND r.athlete_id = sub_a.id AND NOT r.result_ms = " . MAX_INT . "  
        AND r.stroke = '$stroke' AND UPPER(sub_a.gender) = UPPER('$gender')
        GROUP BY r.distance, sub_a.id ORDER by sub_a.last_name, sub_a.first_name, r.distance;");

        return $query->result();
    }

    /**
     * Converts rank timestring to milliseconds
     * @param type $rank_time for example 1.00,00
     * @return rank time in milliseconds
     */
    private function rank_to_ms($rank_time) {

        $ms = 0.0;
        $rank_time = str_replace('.', ':', $rank_time);
        $rank_time = str_replace(',', '.', $rank_time);
        $splits = explode(':', $rank_time);
        if (count($splits) == 2) {
            $ms += (int) $splits[0] * 60 + (double) $splits[1];
        } else if (count($splits) == 1) {
            $ms += (double) $splits[0];
        } else {
            return MAX_INT;
        }

        return $ms * 1000.0;
    }

    /**
     * Converts file of ranks to data and inserts to database. Does not handle
     * incorrect input data!
     * @param array $ranks array from file()
     * @param type $gender rank gender
     */
    function insert_rank_table_to_db(array $ranks, $gender) {

        $rank_types = array('Eliit', 'Meister', 'I järk', 'II järk', 'III järk');
        $strokes = array('vabalt' => 'free', 'selili' => 'back', 'rinnuli' => 'breast', 'liblikat' => 'fly', 'kompleksi' => 'medley');

        $data = array();

        $ignore_row = true;
        foreach ($ranks as $row) {

            if ($ignore_row) {
                $ignore_row = false;
                continue;
            }

            $values = array();
            $line = explode(' ', $row);
            foreach ($line as &$val) {
                $val = trim($val);
                if (strlen($val) != 0 && strlen($val) != 1) {
                    $values[] = $val;
                }
            }
            for ($j = 2; $j < count($values); $j++) {
                $distance = $values[0];
                $stroke = $strokes[$values[1]];
                $result = $this->rank_to_ms($values[$j]);
                $type = $rank_types[$j - 2];
                $type_code = $j - 2;
                $data[] = array('gender' => $gender, 'distance' => $distance, 'stroke' => $stroke, 'time_ms' => $result, 'type' => $type, 'type_code' => $type_code);
            }
        }
        $this->db->insert_batch('eul_ranks', $data);
    }

}

?>
